package mes.kanban.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import mes.kanban.entity.Customer;
import mes.kanban.entity.MesMaterial;
import mes.kanban.entity.SelectVo;
import mes.kanban.exception.MyException;
import mes.kanban.respository.CustomerRespository;
import mes.kanban.respository.MaterialRespository;
import mes.kanban.service.MaterialServiceI;
import mes.kanban.util.FileUitl;
import mes.kanban.util.PageBean;
import mes.kanban.vo.MesMaterialVO;

@Service
public class MaterialServiceImpl implements MaterialServiceI {
	
	private static final Logger log = LoggerFactory.getLogger(MaterialServiceImpl.class);
	
	@Autowired
	private MaterialRespository materialRespository;
	
	@Autowired
	private CustomerRespository customerRespository;
	
	
	@PersistenceContext
	private EntityManager em;

	@Override
	public List<MesMaterial> getDatas(String userId) {
		
		log.info("MaterialServiceImpl getDatas 查询数据开始");
		
		//List<MesMaterial> jpalist = materialRespository.findAll();
		List<MesMaterial> jpalist = materialRespository.findMaterialByCreator(userId);
		return jpalist;
		
	}
	
	@Override
	public void add(MesMaterial m) {
		
		this.materialRespository.save(m);
		
	}

	@Override
	public MesMaterialVO findMaterialById(String materialId) {
		
		MesMaterial material = materialRespository.findOne(materialId);
		MesMaterialVO vo = new MesMaterialVO();
		BeanUtils.copyProperties(material, vo);
		return vo;
		
	}
	
	@Override
	public PageBean getDatasForPageBean(Integer pageNum,Integer pageSize,String userId) {
		
		PageBean pb = new PageBean();
		
		/*Pageable pa = new PageRequest(pageNum-1, pageSize);
		Page<MesMaterial> pages = this.materialRespository.findAll(pa);
		List<MesMaterial> mats = pages.getContent();
		int allrow = (int) pages.getTotalElements();
		int totalPage = pages.getTotalPages();*/
		List<MesMaterial> list = materialRespository.findMaterialByCreator(userId);
		List<MesMaterial> mats = this.materialRespository.findMaterialListByPage((pageNum-1)*pageSize,pageSize,userId);
		
		int allrow = list.size();
		int totalPage = (allrow/pageSize) + (allrow%pageSize==0?0:1);
		pb.setList(mats);
		pb.setAllCounts(allrow);
		pb.setTotalPage(totalPage);
		pb.setPageSize(pageSize);
		pb.setCurrentPage(pageNum);
		
		return pb;
	}
	@Override
	public PageBean toSearch(Integer pageNum,Integer pageSize,String userId,String name,String type) {
		
		PageBean pb = new PageBean();
		
		/*Pageable pa = new PageRequest(pageNum-1, pageSize);
		Page<MesMaterial> pages = this.materialRespository.findAll(pa);
		List<MesMaterial> mats = pages.getContent();
		int allrow = (int) pages.getTotalElements();
		int totalPage = pages.getTotalPages();*/
		String conditionStr = "";
		if(name!=null && name.trim() != ""){
			conditionStr = conditionStr + " and m.name like '%"+name+"%' ";
		}
		if(type!=null &&  type.trim() != ""){
			conditionStr = conditionStr + " and m.material_type LIKE '%"+type+"%' ";
		}
		
		List<MesMaterial> mats = new ArrayList<MesMaterial>();
		
		
		 String sqlList = "SELECT DISTINCT m.ID,m.name,m.material_name,m.save_url,m.create_time,m.creator,m.creator_Name,m.material_type,m.material_type_desc,m.memo   FROM mes_material m LEFT JOIN mes_user u ON u.id = m.creator "
		 		+ "LEFT JOIN mes_user_role ur ON ur.user_id = u.id LEFT JOIN mes_role r ON r.id = ur.role_id INNER JOIN "
		 		+ "( SELECT r.id FROM mes_user u LEFT JOIN mes_user_role ur ON u.id = ur.user_id LEFT JOIN mes_role r ON ur.role_id = r.id WHERE u.id = '"+userId+"' ) "
		 		+ "ur1 ON ur1.id = r.id "+conditionStr+" order by m.create_time desc  " ;
		 String sqlListPage = "SELECT DISTINCT m.ID,m.name,m.material_name,m.save_url,m.create_time,m.creator,m.creator_Name,m.material_type,m.material_type_desc,m.memo   FROM mes_material m LEFT JOIN mes_user u ON u.id = m.creator "
		 		+ "LEFT JOIN mes_user_role ur ON ur.user_id = u.id LEFT JOIN mes_role r ON r.id = ur.role_id INNER JOIN "
		 		+ "( SELECT r.id FROM mes_user u LEFT JOIN mes_user_role ur ON u.id = ur.user_id LEFT JOIN mes_role r ON ur.role_id = r.id WHERE u.id = '"+userId+"' ) "
		 		+ "ur1 ON ur1.id = r.id "+conditionStr+" order by m.create_time desc limit "+(pageNum-1)*pageSize+","+pageSize+" ";
		Query queryList = em.createNativeQuery(sqlList);  
		Query queryPage = em.createNativeQuery(sqlListPage); 
		List<Object[]> resultList = queryList.getResultList();
		List<Object[]> resultPage = queryPage.getResultList();
		for (Object[] row : resultPage) {
			MesMaterial mesMaterial = new MesMaterial();
			mesMaterial.setId((String)row[0]);
			mesMaterial.setName((String)row[1]);
			mesMaterial.setMaterialName((String)row[2]);
			mesMaterial.setUrl((String)row[3]);
			mesMaterial.setCreateTime((Date)row[4]);
			mesMaterial.setCreator((String)row[5]);
			mesMaterial.setCreatorName((String)row[6]);
			mesMaterial.setCreatorType((String)row[7]);
			mesMaterial.setCreatorTypeDesc((String)row[8]);
			mesMaterial.setMemo((String)row[9]);
			mats.add(mesMaterial);
			
		}
		
		int allrow = resultList.size();
		int totalPage = (allrow/pageSize) + (allrow%pageSize==0?0:1);
		pb.setList(mats);
		pb.setAllCounts(allrow);
		pb.setTotalPage(totalPage);
		pb.setPageSize(pageSize);
		pb.setCurrentPage(pageNum);
		
		return pb;
	}
	
	@Override
	public void update(MesMaterial mesMaterial) {
		materialRespository.saveAndFlush(mesMaterial);
	}


	@Override
	public void deleteMaterialById(String id,String userId) {
		
		
		MesMaterial material = materialRespository.findOne(id);
		
		if (material.getCreator().equals(userId)) {
			
			String saveUrl = material.getUrl()+"/"+material.getMaterialName();
			//删除文件
			FileUitl.deleteFileForWindows(saveUrl);
			
			this.materialRespository.delete(id);
		}else{
			throw new MyException("不是登录用户上传的资源,不能删除");
		}
		
		
	}
	
	@Override
	public List<SelectVo> getcompy(String id,String userId) {
		
		List<SelectVo> selectVos = new ArrayList<SelectVo>();
		List<Customer> jpalist = customerRespository.findAll();
		for(int i = 0 ;i<jpalist.size();i++){
			SelectVo selectVo = new SelectVo();
			selectVo.setId(jpalist.get(i).getId());
			selectVo.setName(jpalist.get(i).getNameDetails());
			selectVos.add(selectVo);
		}
		
		return selectVos;
		
	}

	
}
